﻿
using System;
using System.Collections.Generic;
using System.Text;
using OgilvyOne.IDAL;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.Model;

namespace OgilvyOne.SqlServerDAL
{
    public class RoleModel : IRole
    {
        string connstr = SQLHelper.CONN_STRING_NON_DTC;
        #region IRole 成员

        public bool Exists(string rolename,int rid)
        {
            string SQL_SELECTById = "select * from [CMS_ROLE] where ROLENAME=@ROLENAME and RID not in ("+rid+")";
            SqlParameter param = new SqlParameter("@ROLENAME", rolename);
            return int.Parse(SQLHelper.ExecuteScalar(connstr, CommandType.Text, SQL_SELECTById, param).ToString()) > 0;
        }

        public int Add(OgilvyOne.Model.RoleModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into CMS_ROLE(");
            strSql.Append("ROLENAME,REMARK,ADDTIME)");
            strSql.Append(" values (");
            strSql.Append("@ROLENAME,@REMARK,@ADDTIME)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@ROLENAME", SqlDbType.VarChar,50),
					new SqlParameter("@REMARK", SqlDbType.VarChar,1000),
					new SqlParameter("@ADDTIME", SqlDbType.DateTime)};
            parameters[0].Value = model.Rolename;
            parameters[1].Value = model.Remark;
            parameters[2].Value = model.Addtime;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters);
        }

        public bool Update(OgilvyOne.Model.RoleModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update CMS_ROLE set ");
            strSql.Append("ROLENAME=@ROLENAME,");
            strSql.Append("REMARK=@REMARK,");
            strSql.Append("ADDTIME=@ADDTIME");
            strSql.Append(" where RID=@RID");
            SqlParameter[] parameters = {
					new SqlParameter("@ROLENAME", SqlDbType.VarChar,50),
					new SqlParameter("@REMARK", SqlDbType.VarChar,1000),
					new SqlParameter("@ADDTIME", SqlDbType.DateTime),
					new SqlParameter("@RID", SqlDbType.Int,4)};
            parameters[0].Value = model.Rolename;
            parameters[1].Value = model.Remark;
            parameters[2].Value = model.Addtime;
            parameters[3].Value = model.Rid;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0; 
        }

        public bool Delete(string name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CMS_ROLE ");
            strSql.Append(" where ROLENAME=@ROLENAME");
            SqlParameter[] parameters = {
					new SqlParameter("@ROLENAME",  SqlDbType.VarChar,50)
            };
            parameters[0].Value = name;

            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;
        }

        public OgilvyOne.Model.RoleModel GetModel(string name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 RID,ROLENAME,REMARK,ADDTIME from CMS_ROLE ");
            strSql.Append(" where ROLENAME=@ROLENAME");
            SqlParameter[] parameters = {
					new SqlParameter("@ROLENAME", SqlDbType.VarChar,50)
};
            parameters[0].Value = name;

            OgilvyOne.Model.RoleModel model = new OgilvyOne.Model.RoleModel();
            using (SqlDataReader dr = SQLHelper.ExecuteReader(connstr, CommandType.Text, strSql.ToString(), parameters))
            {
                while (dr.Read())
                {
                    if (!dr.IsDBNull(0))
                    {
                        model.Rid = dr.GetInt32(0);
                    }
                    if (!dr.IsDBNull(1))
                    {
                        model.Rolename = dr.GetString(1);
                    }
                    if (!dr.IsDBNull(2))
                    {
                        model.Remark = dr.GetString(2);
                    }
                    if (!dr.IsDBNull(3))
                    {
                        model.Addtime = dr.GetDateTime(3);
                    }
                }
                return model;
            }
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select RID,ROLENAME,REMARK,ADDTIME ");
            strSql.Append(" FROM CMS_ROLE ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SQLHelper.Query(strSql.ToString());
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" RID,ROLENAME,REMARK,ADDTIME ");
            strSql.Append(" FROM CMS_ROLE ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return SQLHelper.Query(strSql.ToString());
        }

        #endregion


        #region IRole 成员


        public bool DeleteList(string Ridlist)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CMS_ROLE ");
            strSql.Append(" where RID in (" + Ridlist + ")  ");
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), null) > 0;
        }

        #endregion
    }
}
